package fr.geobert.prediction;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.util.StringTokenizer;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * This is the core system The algorithm is in fetchMatchingInfo
 * 
 * The other method are for db creation and filling
 * 
 * I'll try to explain my system:
 * 
 * You have a basic dictionnary in the table called "words" whith the displayed
 * form of the word and a normalized form (without accent). AS we have an SQLite
 * db, each word has a _id as primary key by which it will be refered
 * 
 * The purpose of the others tables is to find appropriate candidates even
 * before a word is type.
 * 
 * The entry table "nexts" gives for a word, the known following words. We look
 * for parent_id (which is 0 for the first word of the sentence).
 * 
 * An alternative entry is the table "positions" which for an index in the current
 * sentence, gives a list of candidates. Maybe you can combine with the nexts
 * table to get more accurate result but it will makes the db bigger.
 * 
 * The table "freqs" must be seen as
 * "frequency of a word according to precedent word" and help to sort the words
 * in order to propose the more used words.
 * 
 * So we get a Cursor with the candidates to display.
 * 
 * TODO : there are some TODO in the code, but here are the points I can remember:
 * Manage cursor move
 * deletion of text
 * case where no candidates is found (maybe consider this case like parent_id = 0)
 * learning of new word
 * learning of existing word
 * cleanup mecanism to makes the db smaller.
 * 
 * @author geob
 * 
 */
public class LangDbAdapter {
	private static final String TAG = "Prediction";
	protected static final String DATABASE_NAME = "French.db";
	protected static final int DATABASE_VERSION = 1;

	public static final String WORDS_TABLE = "words";
	public static final String NEXT_TABLE = "next_words";
	public static final String FREQS_TABLE = "words_freqs";
	public static final String POS_TABLE = "words_positions";

	public static final String KEY_WORDS_DISPLAY = "displayed";
	public static final String KEY_WORDS_NORM = "normalized";

	public static final String KEY_NEXT_WORD_ID = "word_id";
	public static final String KEY_NEXT_FOLLOWERS = "followers_ids";

	public static final String KEY_FREQS_WORD_ID = "word_id";
	public static final String KEY_FREQS_PARENT_ID = "parent_id";
	public static final String KEY_FREQS_FREQ = "frequency";

	public static final String KEY_POS_POSITION = "position";
	public static final String KEY_POS_WORDS = "word_ids";

	private static final String CREATE_WORDS_TABLE = "create table " + WORDS_TABLE
			+ "( _id integer primary key autoincrement, " + KEY_WORDS_DISPLAY + " text not null, "
			+ KEY_WORDS_NORM + " text not null);";

	// KEY_NEXT_FOLLOWERS : comma separated word_ids
	private static final String CREATE_NEXT_TABLE = "create table " + NEXT_TABLE
			+ "( _id integer primary key autoincrement, " + KEY_NEXT_WORD_ID + " number not null, "
			+ KEY_NEXT_FOLLOWERS + " text not null);";

	private static final String CREATE_FREQS_TABLE = "create table " + FREQS_TABLE
			+ "( _id integer primary key autoincrement, " + KEY_FREQS_WORD_ID
			+ " number not null, " + KEY_FREQS_PARENT_ID + " number not null, " + KEY_FREQS_FREQ
			+ " number not null);";

	// KEY_POS_WORDS : comma separated word_ids
	private static final String CREATE_POS_TABLE = "create table " + POS_TABLE
			+ "( _id integer primary key autoincrement, " + KEY_POS_POSITION + " number not null, "
			+ KEY_POS_WORDS + " text not null);";

	// Index to speedup things (makes the DB bigger though)
	protected static final String CREATE_INDEX_ON_PARENT_ID_IN_FREQS = "CREATE INDEX IF NOT EXISTS freqs_parent_id_idx ON "
			+ FREQS_TABLE + "(" + KEY_FREQS_PARENT_ID + ")";
	protected static final String CREATE_INDEX_ON_WORD_ID_IN_FREQS = "CREATE INDEX IF NOT EXISTS freqs_word_id_idx ON "
			+ FREQS_TABLE + "(" + KEY_FREQS_WORD_ID + ")";
	protected static final String CREATE_INDEX_ON_WORD_ID_IN_NEXT = "CREATE INDEX IF NOT EXISTS next_word_id_idx ON "
			+ NEXT_TABLE + "(" + KEY_NEXT_WORD_ID + ")";

	protected DatabaseHelper mDbHelper;
	protected SQLiteDatabase mDb;
	protected Activity mCtx;
	public static String sentence_separator = ".!?;";
	public static String word_separator = " .,;";

	private static LangDbAdapter mInstance = null;

	private LangDbAdapter() {

	}

	private void init(Activity ctx) {
		Log.d(TAG, "delete database " + ctx.deleteDatabase(DATABASE_NAME));
		this.mCtx = ctx;
	}

	public static LangDbAdapter getInstance(Activity ctx) {
		if (null == mInstance) {
			mInstance = new LangDbAdapter();
			mInstance.init(ctx);
		}

		return mInstance;
	}

	protected class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.d(TAG, "onCreate db ");
			db.execSQL(CREATE_WORDS_TABLE);
			db.execSQL(CREATE_NEXT_TABLE);
			db.execSQL(CREATE_FREQS_TABLE);
			db.execSQL(CREATE_POS_TABLE);
			db.execSQL(CREATE_INDEX_ON_PARENT_ID_IN_FREQS);
			db.execSQL(CREATE_INDEX_ON_WORD_ID_IN_FREQS);
			db.execSQL(CREATE_INDEX_ON_WORD_ID_IN_NEXT);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion
					+ ", converting to new database");
			// TODO
		}
	}

	public LangDbAdapter open() throws SQLException {

		if (null == mDbHelper) {
			Log.d(TAG, "open database ");
			mDbHelper = new DatabaseHelper(mCtx);
			mDb = mDbHelper.getWritableDatabase();
			initData();
		}
		return this;
	}

	public void close() {
		if (null != mDbHelper) {
			mDbHelper.close();
			mDbHelper = null;
			mDb.close();
			mDb = null;
		}
	}

	public void initData() {
		try {
			FileInputStream fstream = new FileInputStream("/sdcard/corpus.txt");
			// Get the object of DataInputStream
			DataInputStream in = new DataInputStream(fstream);
			BufferedReader br = new BufferedReader(new InputStreamReader(in));
			String strLine;
			// Read File Line By Line
			while ((strLine = br.readLine()) != null) {
				if (strLine.charAt(0) != '#')
					processLine(strLine);
			}
			// Close the input stream
			in.close();
		} catch (Exception e) {// Catch exception if any
			Log.e(TAG, "initData error " + e.toString());
		}
	}

	private void processLine(String strLine) {
		Log.d(TAG, "processLine strLine [" + strLine + "]");
		long parentId = 0;
		int pos = 0;
		StringTokenizer tok = new StringTokenizer(strLine, word_separator);
		while (tok.hasMoreTokens()) {
			String word = tok.nextToken();
			Log.d(TAG, "processLine word [" + word + "]");
			long id = doesWordExist(word);
			if (id <= 0) {
				id = createWord(word);
			}
			addFreqForWordIfNeeded(id, parentId);
			addPositionForWordIfNeeded(id, pos);
			addNextForParentIfNeeded(parentId, id);
			parentId = id;
			pos++;
		}
	}

	private void addNextForParentIfNeeded(long parentId, long id) {
		Log.d(TAG, "addNextForParentIfNeeded parentId " + parentId + "/ id : " + id);
		Cursor c = mDb.query(NEXT_TABLE, new String[] { KEY_NEXT_FOLLOWERS }, KEY_NEXT_WORD_ID
				+ "=" + parentId, null, null, null, null);
		if (null == c || !c.moveToFirst()) {
			setWordFollowers(parentId, String.valueOf(id));
		} else {
			String ids = c.getString(0);
			ids = completeWordsListIFNeeded(id, ids);
			if (null != ids)
				updateWordFollowers(parentId, ids);
		}
		c.close();
	}

	private String completeWordsListIFNeeded(long idToAdd, String ids) {
		StringTokenizer tok = new StringTokenizer(ids, ",");
		boolean found = false;
		while (tok.hasMoreTokens()) {
			String i = tok.nextToken();
			if (i.equalsIgnoreCase(String.valueOf(idToAdd))) {
				found = true;
				break;
			}
		}
		if (!found) {
			ids = ids + "," + String.valueOf(idToAdd);
			return ids;
		}
		return null;
	}

	private void addPositionForWordIfNeeded(long id, int pos) {
		Cursor c = mDb.query(POS_TABLE, new String[] { KEY_POS_WORDS }, KEY_POS_POSITION + "="
				+ pos, null, null, null, null);

		if (null == c || !c.moveToFirst()) {
			setWordPosition(pos, String.valueOf(id));
		} else {
			String ids = c.getString(0);
			ids = completeWordsListIFNeeded(id, ids);
			if (null != ids)
				updateWordPosition(pos, ids);

		}
		c.close();
	}

	public void addFreqForWordIfNeeded(long wordId, long parentId) {
		Cursor c = mDb.query(FREQS_TABLE, new String[] { KEY_FREQS_WORD_ID, KEY_FREQS_PARENT_ID,
				KEY_FREQS_FREQ }, KEY_FREQS_WORD_ID + "=" + wordId + " AND " + KEY_FREQS_PARENT_ID
				+ "=" + parentId, null, null, null, null);
		if (null == c || !c.moveToFirst()) {
			setWordFreq(wordId, parentId, 0);
		} else {
			incWordFreq(wordId, parentId);
		}
		c.close();
	}

	public long doesWordExist(String word) {
		word = AsciiUtils.convertNonAscii(word);
		Cursor c = mDb.query(WORDS_TABLE, new String[] { "_id" }, KEY_WORDS_NORM + "=\"" + word
				+ "\"", null, null, null, null);
		if (null != c) {
			if (c.moveToFirst()) {
				long l = c.getLong(0);
				c.close();
				return l;
			}
			c.close();
		}
		return 0;
	}

	public long createWord(String word) {
		Log.d(TAG, "processLine createWord [" + word + "]");
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_WORDS_DISPLAY, word);
		initialValues.put(KEY_WORDS_NORM, AsciiUtils.convertNonAscii(word));
		return mDb.insert(WORDS_TABLE, null, initialValues);
	}

	public void setWordFollowers(long id, String followers) {
		Log.d(TAG, "setWordFollowers followers " + followers + "/ for word_id " + id);
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_NEXT_WORD_ID, id);
		initialValues.put(KEY_NEXT_FOLLOWERS, followers);
		mDb.insert(NEXT_TABLE, null, initialValues);
	}

	public void updateWordFollowers(long id, String followers) {
		Log.d(TAG, "updateWordFollowers id " + id + "/ followers " + followers);
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_NEXT_FOLLOWERS, followers);
		mDb.update(NEXT_TABLE, initialValues, KEY_NEXT_WORD_ID + "=" + id, null);
	}

	public void setWordPosition(int pos, String words) {
		Log.d(TAG, "setWordPosition words " + pos + "/" + words);
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_POS_POSITION, pos);
		initialValues.put(KEY_POS_WORDS, words);
		mDb.insert(POS_TABLE, null, initialValues);
	}

	public void updateWordPosition(int pos, String words) {
		Log.d(TAG, "updateWordPosition words " + pos + "/" + words);
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_POS_WORDS, words);
		mDb.update(POS_TABLE, initialValues, KEY_POS_POSITION + "=" + pos, null);
	}

	public void setWordFreq(long id, long parent_id, int freq) {
		Log.d(TAG, "setWordFreq id " + id + "/" + parent_id + "/" + freq);
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_FREQS_WORD_ID, id);
		initialValues.put(KEY_FREQS_PARENT_ID, parent_id);
		initialValues.put(KEY_FREQS_FREQ, freq);
		mDb.insert(FREQS_TABLE, null, initialValues);
	}

	public void incWordFreq(long id, long parent_id) {
		Log.d(TAG, "incWordFreq id " + id + "/" + parent_id);
		Cursor c = mDb.query(FREQS_TABLE, new String[] { KEY_FREQS_FREQ }, KEY_FREQS_WORD_ID + "="
				+ id + " AND " + KEY_FREQS_PARENT_ID + "=" + parent_id, null, null, null, null);
		if (null != c) {
			if (c.moveToFirst()) {
				int f = c.getInt(0);
				ContentValues initialValues = new ContentValues();
				initialValues.put(KEY_FREQS_FREQ, f + 1);
				mDb.update(FREQS_TABLE, initialValues, KEY_FREQS_WORD_ID + "=" + id + " AND "
						+ KEY_FREQS_PARENT_ID + "=" + parent_id, null);
			}
			c.close();
		}
	}

	public void deleteWord(long rowId) {
		// TODO : del all occurence of row id in each table
	}

	final static String[] candidatesCols = { "freqs." + KEY_FREQS_WORD_ID /* 0 */,
			KEY_FREQS_PARENT_ID /* 1 */, KEY_FREQS_FREQ /* 2 */, KEY_WORDS_DISPLAY /* 3 */,
			"words._id" /* 4 */};

	final static String candidatesJointure = FREQS_TABLE + " freqs LEFT OUTER JOIN " + WORDS_TABLE
			+ " words ON freqs." + KEY_FREQS_WORD_ID + " = words._id";

	final static String candidatesOrdering = "freqs." + KEY_FREQS_FREQ + " desc, words."
			+ KEY_WORDS_DISPLAY + " asc";

	public Cursor fetchMatchingInfo(String currentWord, long parentId, int pos) {
		Cursor res = null;

		currentWord = AsciiUtils.convertNonAscii(currentWord.trim());
		Log.d(TAG, "fetchMatchingInfo currentWord [" + currentWord + "]/ parent " + parentId
				+ "/ pos: " + pos);
		// get candidates by pos in sentence
		Cursor c = mDb.query(NEXT_TABLE, new String[] { KEY_NEXT_FOLLOWERS }, KEY_NEXT_WORD_ID
				+ "=" + parentId, null, null, null, null);
		if (null != c) {
			if (c.moveToFirst()) {
				String ids = c.getString(0);
				Log.d(TAG, "fetchMatchingInfo has candidates from nexts : " + ids);
				if (currentWord.length() == 0) {
					Log.d(TAG, "fetchMatchingInfo word not typed yet ");
					// word not type yet, use only pos to get candidates and
					// JOIN with freqs table for filter by precedent word and
					// sort by freq
					res = mDb.query(candidatesJointure, candidatesCols, "freqs."
							+ KEY_FREQS_WORD_ID + " in(" + ids + ") AND " + KEY_FREQS_PARENT_ID
							+ "=" + parentId, null, null, null, candidatesOrdering);
				} else {
					Log.d(TAG, "fetchMatchingInfo word has begun ");
					// word begin to be typed, add the letter to the where
					// clause
					res = mDb.query(candidatesJointure, candidatesCols, "freqs."
							+ KEY_FREQS_WORD_ID + " in(" + ids + ") AND " + KEY_FREQS_PARENT_ID
							+ "=" + parentId + " AND words." + KEY_WORDS_NORM + " LIKE \""
							+ currentWord + "%\"", null, null, null, candidatesOrdering);
				}
			} else {
				Log.d(TAG, "fetchMatchingInfo NO candidates from nexts ");
				// no candidates at given pos, rely only on the words table
				Cursor c2 = mDb.query(WORDS_TABLE, new String[] { "_id" }, KEY_WORDS_NORM
						+ " LIKE \"" + currentWord + "%\"", null, null, null, null);
				StringBuilder idsBuilder = new StringBuilder();
				if (c2.moveToFirst()) {
					// found words begining like currentWord, build ids string
					do {
						idsBuilder.append(c2.getInt(0)).append(',');
					} while (c2.moveToNext());
					idsBuilder.setLength(idsBuilder.length() - 1);
					Log.d(TAG, "fetchMatchingInfo idsBuilder " + idsBuilder.toString());
					res = mDb.query(candidatesJointure, candidatesCols, "freqs."
							+ KEY_FREQS_WORD_ID + " in(" + idsBuilder.toString() + ") AND "
							+ KEY_FREQS_PARENT_ID + "=" + parentId + " AND words." + KEY_WORDS_NORM
							+ " LIKE \"" + currentWord + "%\"", null, null, null,
							candidatesOrdering);
				} else {
					// no words in dictionnary, no candidates at position, need
					// to find another strategy here, consider as new sentence?
					// (that's to say position = 0)
				}
			}
			c.close();
		} else {
			// no cursor, should not happen
		}
		if (res != null) {
			Log.d(TAG, "fetchMatchingInfo res nelt : " + res.getCount());
		} else {
			Log.d(TAG, "fetchMatchingInfo res is null ");
		}
		mCtx.startManagingCursor(res);
		return res;
	}

	public long getParentId(String string) {
		string = AsciiUtils.convertNonAscii(string);
		Log.d(TAG, "getParentId string [" + string + "]");
		Cursor c = mDb.query(WORDS_TABLE, new String[] { "_id" }, KEY_WORDS_NORM + "=\"" + string
				+ "\"", null, null, null, null);
		long res = 0;
		if (null != c) {
			if (c.moveToFirst()) {
				res = c.getLong(0);
			}
			c.close();
		}
		return res;
	}

}
